package fregat.connector.util;

import fregat.base.database.DatabaseConnector;
import fregat.base.database.Sid;
import fregat.base.util.Logger;
import fregat.connector.ConnectorService;

import java.io.File;
import java.io.FileOutputStream;
import java.io.PrintStream;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
import java.util.StringTokenizer;
import java.util.Vector;

/**
 * Converter that creates prototype telegram classes from the database
 * <code>IMPORT</code> table using a given <code>TABNAME</code>.
 *
 * @author Alexander Mann <br><b><i>ECO_L O G</i> &nbsp Logistiksysteme GmbH</b>
 * @version 1.0
 */
public class Converter {
  private static DatabaseConnector connector;
  private static String filler = "";
  private static String ignore = "";
  private static boolean createFunctions;
  private static boolean createClasses;
  private static boolean createViews;

  private static final String SEPARATOR = // line separator
    (String) java.security.AccessController.doPrivileged(new sun.security.action.GetPropertyAction("line.separator"));

  public static ResourceBundle resource
    = ResourceBundle.getBundle("at.ecolog.connector.resource"); // text resources

  private Converter() {
  }

  /**
   * The converter allows the following arguments:<br>
   * <code>-tabname &lt;tabname&gt; [&lt;tabname&gt; ...]</code> ... Telegram types (<code>SSTTYP</code>)<br>
   * <code>-tabname all</code> ... fetch all telegram types from <code>SSTTYP</code><br>
   * <code>-sql</code> ... create SQL 'create'-functions<br>
   * <code>-view</code> ... create SQL views<br>
   * <code>-java</code> ... create Java classes<br>
   * <code>-db &lt;name&gt; &lt;user&gt; &lt;password&gt;</code> ... connect to database (default sysadm@lms)<br>
   * <code>-filler &lt;text&gt; &lt;text&gt; ... </code> ... all columns containing the text are interpreted as filler (no set- and get-method but used for telegram creation<br>
   * <code>-ignore &lt;colname&gt; &lt;colname&gt; ...</code> ... these columns are ignored<br>
   * <br>eg.<br>
   * <code>Converter -tabname BEH ZPI -sql -java -filler RESERVE -ignore TYPE,SEQUENCE</code>
   * will create Java classes and SQL functions for the telegram types 'BEH' and 'ZPI' ignoring
   * the fields 'TYPE' and 'SEQUENCE', all columns containing 'RESERVE' in their names are interpreted
   * as fillers.
   *
   * @param args see above
   */
  public static void main(String[] args) {
    Logger.getDefaultCategory().setLevel(3);
    Logger.getDefaultCategory().logToConsole(true);

    if (args.length >= 2) {
      // interpret arguments -->
      String arguments = args[0];
      for (int i = 1; i < args.length; i++) {
        arguments += " " + args[i];
      }
      StringTokenizer tokenizer = new StringTokenizer(arguments, "-");

      boolean allTypes = false;

      String tabnames = "";
      String database = "lms";
      String user = "sysadm";
      String password = "sysadm";

      while (tokenizer.hasMoreTokens()) {
        String argument = tokenizer.nextToken().trim();
        if (argument.toUpperCase().startsWith("TABNAME")) {
          // which telegrams should be created
          argument = argument.substring(argument.indexOf(" "), argument.length()).trim();
          if (argument.equalsIgnoreCase("ALL")) {
            allTypes = true;
          } else {
            tabnames = argument;
          }
        } else if (argument.toUpperCase().startsWith("DB")) {
          // which database should be used
          StringTokenizer dbTokenizer = new StringTokenizer(argument);
          dbTokenizer.nextToken();
          database = dbTokenizer.nextToken();
          user = dbTokenizer.nextToken();
          password = dbTokenizer.nextToken();
        } else if (argument.toUpperCase().startsWith("FILLER")) {
          // which fields (colname) should be interpreted as filler
          filler = argument.substring(argument.indexOf(" "), argument.length()).trim();
        } else if (argument.toUpperCase().startsWith("IGNORE")) {
          // which fields (colname) should be ignored
          ignore = argument.substring(argument.indexOf(" "), argument.length()).trim();
        } else if (argument.equalsIgnoreCase("SQL")) {
          // create SQL functions
          createFunctions = true;
        } else if (argument.equalsIgnoreCase("JAVA")) {
          // create Java classes
          createClasses = true;
        } else if (argument.equalsIgnoreCase("VIEW")) {
// create SQL views
          createViews = true;
        } else {
          System.out.println(resource.getString("Converter_main_1") + " " + argument);
          return;
        }
      }
      // <--

      connector = new DatabaseConnector(database, user, password,new Sid("lms"),1521);
      connector.connectToDatabase();
      connector.setName(ConnectorService.VERSION);
      connector.setAction("Converter");
      Converter converter = new Converter();

      if (allTypes) {
        // selects all telegram types
        String sql = "select tabname from ssttyp";

        try {
          CallableStatement statement = connector.prepareStatement("ssttyp", sql);
          ResultSet result = statement.executeQuery();
          StringBuffer buffer = new StringBuffer();
          while (result.next()) {
            String name = result.getString(1);
            if (name != null) {
              buffer.append(name);
              buffer.append(" ");
            }
          }
          tabnames = buffer.toString().trim();

        } catch (SQLException ex) {
          ex.printStackTrace();
        }
      }

      System.out.println(resource.getString("Converter_main_2") + " = [" + tabnames.replace(' ', ',') + "]");
      StringTokenizer tabname = new StringTokenizer(tabnames);

      // create classes and SQL functions
      while (tabname != null && tabname.hasMoreTokens()) {
        String name = tabname.nextToken();
        if (createClasses) {
          converter.createClass(name);
        }
        if (createFunctions) {
          converter.createFunction(name);
        }
        if (createViews) {
          converter.createView(name);
        }
      }

      connector.shutdown();
      return;
    }
    System.out.println(resource.getString("Converter_main_3"));
  }

  private void createView(String tabname) {
    Vector entries = fetchColumns(tabname);
    if (entries.size() > 0) {
      StringBuffer buffer = new StringBuffer(entries.size() * 200);

      appendln(buffer, "create or replace force view SYSADM.V" + tabname);
      appendln(buffer, "  (SSTDAT_ID, SSTTYP, SSTSTAT, DIAGNOSE,");

      StringBuffer values = new StringBuffer(entries.size() * 10);
      int position = 1;
      int characters = 2;

      buffer.append("  ");
      values.append("  ");
      for (int i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String parameter = entry.name + " ,";
          String value = "substr(nutzdaten," + position + "," + entry.length + "),";
          characters += value.length();

          if (characters > 80) {
            appendln(buffer);
            appendln(values);
            buffer.append("  ");
            values.append("  ");
            characters = 2;
          }
          buffer.append(parameter);
          values.append(value);
        }
        position += entry.length;
      }
      appendln(buffer);

      appendln(buffer, "  ERST,ERSTBEN_ID,AEND,AENDBEN_ID)");
      appendln(buffer, "as");
      appendln(buffer, "select");
      appendln(buffer, "  SSTDAT_ID,SSTTYP,SSTSTAT,DIAGNOSE,");
      appendln(buffer, values.toString());
      appendln(buffer, "  ERST,ERSTBEN_ID,AEND,AENDBEN_ID");
      appendln(buffer, "from SSTDAT");
      appendln(buffer, "where SSTTYP = " + fetchType(tabname));

      // write to file
      write(buffer.toString(), "V" + tabname + ".sql");
    } else {
      System.out.println(resource.getString("Converter_createView_1"));
    }
  }

  private void createFunction(String tabname) {
    Vector entries = fetchColumns(tabname);
    if (entries.size() > 0) {
      StringBuffer buffer = new StringBuffer(entries.size() * 200);
      int i = 0;
      int processedEntries = 0;

      String functionName = "create_" + tabname.toLowerCase();

      // function header -->
      appendln(buffer, "CREATE OR REPLACE FUNCTION " + functionName + "(");
      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String parameterName;
          ;
          String parameterType;
          if (entry.numerical) {
            parameterName = "sq";
            parameterType = "number";
          } else {
            parameterName = "sqs";
            parameterType = "varchar2";
          }
          parameterName += firstLetterUppercase(entry.name);

          if (processedEntries != 0) {
            appendln(buffer, ",");
          }

          buffer.append("  " + parameterName + " in " + parameterType);
          processedEntries++;
        }
      }
      appendln(buffer, ") return varchar2 AS");
      appendln(buffer);
      // <--

      // standard declarations -->
      appendln(buffer, "  -- Allgemein");
      appendln(buffer, "  sqsFmt varchar2(65) :=  'FM099999999999999999999999999999999999999999999999999999999999999';");
      appendln(buffer);

      appendln(buffer, "  sqsData varchar2(200) := '';");
      appendln(buffer, "  sqSstId number;");
      appendln(buffer);

      appendln(buffer, "  -- Cursor Variablen");
      appendln(buffer, "  sqsColName varchar2(30);");
      appendln(buffer, "  sqsStdDefault varchar2(30);");
      appendln(buffer, "  sqDezimal number;");
      appendln(buffer, "  sqsDatFormat varchar2(50);");
      appendln(buffer, "  sqLaenge number;");
      appendln(buffer, "  sqStartPos number;");
      appendln(buffer, "  sqsHelpStr varchar2(100) := '';");
      appendln(buffer);

      appendln(buffer, "  -- Cursor fuer Telegrammfelder");
      appendln(buffer, "  cursor cFields is");
      appendln(buffer, "  SELECT upper(ltrim(rtrim(imp1.colname))), ltrim(rtrim(imp1.stddefault)), nvl(imp1.dezimal,0),");
      appendln(buffer, "         nvl(imp1.datformat,'DDMMYYYYHH24MI'),imp1.laenge, SUM(imp2.laenge)+1-imp1.laenge");
      appendln(buffer, "  FROM import imp1, import imp2");
      appendln(buffer, "  WHERE UPPER(ltrim(rtrim(imp1.tabname))) = '" + tabname + "' AND");
      appendln(buffer, "        UPPER(ltrim(rtrim(imp2.tabname))) = '" + tabname + "' AND");
      appendln(buffer, "        imp1.laenge > 0 AND imp2.laenge > 0 AND	imp2.rc <= imp1.rc");
      appendln(buffer, "  GROUP BY imp1.colname, imp1.stddefault, imp1.dezimal, imp1.laenge, imp1.datformat, imp1.rc");
      appendln(buffer, "  ORDER BY imp1.rc;");
      appendln(buffer);
      // <--

      // function body -->
      appendln(buffer, "begin");
      appendln(buffer, "  -- Telegramm erzeugen");
      appendln(buffer, "  open cFields;");
      appendln(buffer, "    loop");
      appendln(buffer, "      fetch cFields into sqsColName, sqsStdDefault, sqDezimal, sqsDatFormat, sqLaenge, sqStartPos;");
      appendln(buffer, "      exit when cFields%notfound;");
      appendln(buffer);

      appendln(buffer, "      sqsHelpStr := '';");
      appendln(buffer);

      processedEntries = 0;

      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String parameterName = "sq";
          if (!entry.numerical) {
            parameterName += "s";
          }
          parameterName += firstLetterUppercase(entry.name);

          if (processedEntries == 0) {
            buffer.append("      if(");
          } else {
            buffer.append("      elsif(");
          }
          appendln(buffer, "sqsColName ='" + entry.name + "') then");
          buffer.append("        sqsHelpStr := ");
          if (entry.numerical) {
            appendln(buffer, "substr(to_char(" + parameterName + ",sqsFmt),(-1)*sqLaenge);");
          } else {
            appendln(buffer, parameterName + ";");
          }
          processedEntries++;
        }
      }
      appendln(buffer, "      end if;");
      appendln(buffer);

      appendln(buffer, "      -- Auffuellen");
      appendln(buffer, "      sqsHelpStr := substr(sqsHelpStr,1,sqLaenge);");
      appendln(buffer, "      for sqIndex in 1..sqLaenge-nvl(length(sqsHelpStr),0) loop");
      appendln(buffer, "        sqsHelpStr := sqsHelpStr || ' ';");
      appendln(buffer, "      end loop;");
      appendln(buffer);

      appendln(buffer, "      -- Anhaengen an Telegramm");
      appendln(buffer, "      sqsData := sqsData||sqsHelpStr;");
      appendln(buffer, "    end loop;");
      appendln(buffer, "  close cFields;");
      appendln(buffer);

      appendln(buffer, "  -- Eintragen in SSTDAT");
      appendln(buffer, "  get_sequence('SSTDAT',0,sqSstId);");

      buffer.append("  insert into SSTDAT (SSTDAT_ID,SSTTYP,SSTSTAT,NUTZDATEN,erst,erstben_id) values (sqSstId,");
      buffer.append(fetchType(tabname));
      appendln(buffer, ",1,sqsData,sysdate,0);");
      appendln(buffer);

      appendln(buffer, "  return sqsData;");
      buffer.append("end " + functionName + ";");
      appendln(buffer);
      // <--

      // write to file
      write(buffer.toString(), functionName.toUpperCase() + ".fnc");
    } else {
      System.out.println(resource.getString("Converter_createFunction_1"));
    }
  }

  private void createClass(String tabname) {
    Vector entries = fetchColumns(tabname);

    if (entries.size() > 0) {
      StringBuffer buffer = new StringBuffer(entries.size() * 200);
      int i = 0;
      int position = 0;

      // class header
      appendln(buffer, "import java.util.*;");
      appendln(buffer, "import at.ecolog.base.util.*;");
      appendln(buffer);
      appendln(buffer, "public class " + tabname + " {");
      appendln(buffer);

      // create members -->
      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String memberClass = entry.numerical ? "NumData" : "AlphaData";
          appendln(buffer, "  " + memberClass + " " + entry.name.toLowerCase() + ";");
        }
      }
      appendln(buffer);
      // <--

      // create receive constructor -->
      appendln(buffer, "  public " + tabname + "(String telegram) {");
      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String memberClass = entry.numerical ? "NumData" : "AlphaData";
          appendln(buffer, "    " + entry.name.toLowerCase() + " = new " + memberClass + "(telegram," + position + "," + entry.length + ");");
        }
        position += entry.length;
      }
      appendln(buffer, "  }");
      appendln(buffer);
      // <--

      // create send constructor -->
      int sendEntries = 0;
      int characters = 0;
      String constructorName = "  public " + tabname + "(";
      int indent = constructorName.length();
      buffer.append(constructorName);
      position = 0;
      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String memberPrimitive = entry.numerical ? "int" : "String";
          if (sendEntries != 0) {
            buffer.append(", ");
          }
          String parameter = memberPrimitive + " " + entry.name.toLowerCase();
          characters += parameter.length();
          buffer.append(parameter);
          if (characters > 80) {
            characters = 0;
            appendln(buffer);

            for (int j = 0; j < indent; j++) {
              buffer.append(" ");
            }
          }
          sendEntries++;
        }
      }
      appendln(buffer, ") {");

      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String memberClass = entry.numerical ? "NumData" : "AlphaData";
          appendln(buffer, "    this." + entry.name.toLowerCase() + " = new " + memberClass + "(" + entry.name.toLowerCase() + "," + entry.length + ");");
        }
      }
      appendln(buffer, "  }");
      appendln(buffer);
      // <--

      // create default constructor -->
      int defaultEntries = 0;
      StringBuffer defaultBuffer = new StringBuffer();
      characters = 0;
      defaultBuffer.append(constructorName);
      position = 0;
      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler && (entry.initial == null)) {
          String memberPrimitive = entry.numerical ? "int" : "String";
          if (defaultEntries != 0) {
            defaultBuffer.append(", ");
          }
          String parameter = memberPrimitive + " " + entry.name.toLowerCase();
          characters += parameter.length();
          defaultBuffer.append(parameter);
          if (characters > 80) {
            characters = 0;
            appendln(defaultBuffer);

            for (int j = 0; j < indent; j++) {
              defaultBuffer.append(" ");
            }
          }
          defaultEntries++;
        }
      }
      appendln(defaultBuffer, ") {");

      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        if (!entry.filler) {
          String memberClass = entry.numerical ? "NumData" : "AlphaData";
          defaultBuffer.append("    this." + entry.name.toLowerCase() + " = new " + memberClass + "(");
          if (entry.initial != null) {
            if (entry.numerical) {
              defaultBuffer.append(entry.initial);
            } else {
              defaultBuffer.append("\"" + entry.initial + "\"");
            }
          } else {
            defaultBuffer.append(entry.name.toLowerCase());
          }
          appendln(defaultBuffer, "," + entry.length + ");");
        }
      }
      appendln(defaultBuffer, "  }");
      appendln(defaultBuffer);

      // only add if different then send constructor
      if (sendEntries != defaultEntries) {
        buffer.append(defaultBuffer.toString());
      }
      // <--

      // create setter and getter -->
      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);

        if (!entry.filler) {
          //String memberClass;
          String memberPrimitive;
          String parameter;
          String setMethod;
          String getMethod;

          if (entry.numerical) {
            //memberClass = "NumData";
            memberPrimitive = "int";
            parameter = "i";
            setMethod = ".setValue(";
            getMethod = ".intValue()";
          } else {
            //memberClass = "AlphaData";
            memberPrimitive = "String";
            parameter = "s";
            setMethod = ".setData(";
            getMethod = ".toString()";
          }
          String methodName = firstLetterUppercase(entry.name);

          // setter
          appendln(buffer, "  public void set" + methodName + "(" + memberPrimitive + " " + parameter + ") {");
          appendln(buffer, "    " + entry.name.toLowerCase() + setMethod + parameter + ");");
          appendln(buffer, "  }");
          appendln(buffer);

          // getter
          appendln(buffer, "  public void get" + methodName + "() {\n");
          appendln(buffer, "    return " + entry.name.toLowerCase() + getMethod + ";");
          appendln(buffer, "  }");

          if (i < entries.size() - 1) {
            appendln(buffer);
          }
        }
      }
      // <--

      // telegram creation -->
      appendln(buffer, "  public String toString() {");
      appendln(buffer, "    StringBuffer buffer = new StringBuffer();");

      for (i = 0; i < entries.size(); i++) {
        Entry entry = (Entry) entries.elementAt(i);
        buffer.append("    buffer.append(");
        if (!entry.filler) {
          appendln(buffer, entry.name.toLowerCase() + ");");
        } else {
          appendln(buffer, "new AlphaData(\"\"," + entry.length + "));");
        }
        position += entry.length;
      }
      appendln(buffer, "    return buffer.toString();");
      appendln(buffer, "  }");
      // <--

      // close class
      buffer.append("}");

      //System.out.println(buffer.toString());

      // write to file
      write(buffer.toString(), tabname + ".java");

    } else {
      System.out.println(resource.getString("Converter_createClass_1"));
    }
    // <--
  }

  private String firstLetterUppercase(String s) {
    return s.substring(0, 1).toUpperCase() + s.substring(1, s.length()).toLowerCase();
  }

  private String fetchType(String tabname) {
    String sql = "select CONST from SSTTYP where TABNAME = '" + tabname + "'";
    int type = -1;
    try {
      CallableStatement statement = connector.prepareStatement("ssttyp", sql);
      ResultSet result = statement.executeQuery();

      while (result.next()) {
        type = result.getInt(1);
      }
    } catch (SQLException ex) {
      ex.printStackTrace();
    }

    if (type > 0) {
      return Integer.toString(type);
    } else {
      return "/* SSTTYP */";
    }
  }

  private Vector fetchColumns(String tabname) {
    // fetch telegram fields from database -->
    Vector entries = new Vector();
    String sql = "select COLNAME,LAENGE,RC,STDDEFAULT,NUMERICAL from IMPORT where TABNAME = '" + tabname + "' order by RC";

    try {
      CallableStatement statement = connector.prepareStatement("import", sql);
      ResultSet result = statement.executeQuery();

      while (result.next()) {

        Entry entry = new Entry();
        entry.name = result.getString(1);

        StringTokenizer ignoreTokenizer = new StringTokenizer(ignore);
        boolean ignore = false;
        while (ignoreTokenizer.hasMoreTokens() && !ignore) {
          ignore = entry.name.toUpperCase().indexOf(ignoreTokenizer.nextToken().toUpperCase()) >= 0;
        }

        if (!ignore) {
          entry.length = result.getInt(2);
          entry.rc = result.getInt(3);
          entry.initial = result.getString(4);
          entry.numerical = result.getInt(5) == 1;

          StringTokenizer fillerTokenizer = new StringTokenizer(filler);
          while (fillerTokenizer.hasMoreTokens() && !entry.filler) {
            entry.filler = entry.name.toUpperCase().indexOf(fillerTokenizer.nextToken().toUpperCase()) >= 0;
          }

          entries.addElement(entry);
        }
      }
      statement.close();

    } catch (SQLException ex) {
      ex.printStackTrace();
    }
    // <--
    return entries;
  }

  private void appendln(StringBuffer buffer, String text) {
    buffer.append(text);
    buffer.append(SEPARATOR);
  }

  private void appendln(StringBuffer buffer) {
    buffer.append(SEPARATOR);
  }

  private void write(String output, String filename) {
    File classFile = new File(filename);
    try {
      PrintStream out = new PrintStream(new FileOutputStream(classFile));
      out.println(output);
      System.out.println(resource.getString("Converter_write_1") + " '" + filename + "'");
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }

  // internal data structure for each field (colname)
  private class Entry {
    String name; // colname
    int length;
    int rc;
    String initial; // default(initial) value
    boolean numerical; // true, if numerical value
    boolean filler; // true, if only filler(reserve)

    public String toString() {
      return name + "," + length + "," + rc + "," + initial + "," + numerical;
    }
  }
}